package com.yy.base.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import com.yy.base.exception.BaseException;

public class PoiExcelUtil {
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//处理时间格式
	private static DecimalFormat df = new DecimalFormat("#.####");//处理浮点格式

	public List<String[]> parseExcel(String filePath) {
		return parseExcel(new File(filePath));
	}
	/**
	 * 解析excel
	 */
	public List<String[]> parseExcel(File file) {
		FileInputStream fileInput = null;
		try {
			fileInput = new FileInputStream(file);
			Workbook workbook = WorkbookFactory.create(fileInput);
			return loadExcel(workbook);
		} catch (Exception e) {
			throw new BaseException(e);
		} finally {
			if (fileInput != null) {
				try {
					fileInput.close();
				} catch (Exception e) {
				}
			}
		}
	}
	/**
	 * 解析excel2007
	 */
	private List<String[]> loadExcel(Workbook workbook) throws Exception {
		List<String[]> contents = new ArrayList<String[]>();
		
		Sheet sheet = null;
		Row row = null;
		String[] rowData = null;
		Cell cell = null;
		int cellType = 0;
		
		int sheetSize = workbook.getNumberOfSheets();
		
		for (int numSheet = 0; numSheet < sheetSize; numSheet++) {
			sheet = workbook.getSheetAt(numSheet);
			if (sheet == null) {
				continue;
			}
			for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
				row = sheet.getRow(rowNum);
				if (row == null) {
					continue;
				}
				//如果到第二个sheet中时去掉第一行的标题
				if (rowNum == 0 && contents.size() > 0) {
					continue;
				}
				int iCellNum = row.getLastCellNum();
				rowData = new String[iCellNum];
				for (int i = 0; i < iCellNum; i++) {
					cell = row.getCell(i);
					if (cell != null) {
						cellType = cell.getCellType();
						if (cellType == Cell.CELL_TYPE_FORMULA) {
							//rowData[i] = cell.getCellFormula();
							cellType = cell.getCachedFormulaResultType();
						}
						
						if (cellType == Cell.CELL_TYPE_NUMERIC) {
							//判断是否为时间格式
							if (DateUtil.isCellDateFormatted(cell)) {
								rowData[i] = sdf.format(cell.getDateCellValue());
							} else {
								Double value = cell.getNumericCellValue();
								if (value > value.longValue()) {//判断是否有小数值，转换为long后的值如果比之前值小则证明有小数
									rowData[i] = df.format(value);
								} else {
									rowData[i] = String.valueOf(value.longValue());
								}
							}
						} else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
							rowData[i] = cell.getBooleanCellValue() ? "是" : "否";
						}  else {
							rowData[i] = cell.getStringCellValue();
						}
					}
				}
				contents.add(rowData);
			}
		}
		return contents;
	}
}